﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        public class cMain
        {
            public static Control Form1Control = null;
            private cMain() { }
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn;
        private SqlConnection competitors;
        private DataTable dt_rep;
        private delegate void Deleg();
        private Deleg del;
        private DateTime date_b;                                    //- Остаток на дату
        private DateTime date_sg_sup;                               //- Не учитывать срок годности от поставщика до (товар, купленный по акции)
        private DateTime date_sg;                                   //- Не учитывать срок годности до (переоцененный товар)
        private int pharm_maks;                                     //- Аптека максавит
        private int flag_rep;                                       //- Флаг отчёта, 0 - относительно закупки, 1 - относительно аптек 
        private DataTable dt_drugs;                                 //- Для списка выбранных товаров
        private DataTable dt_brand = new DataTable();               //- Для брэнда по товарам
        private DataTable dt_pharm = new DataTable();               //- Фильтр по аптекам
        private DataTable dt_drug_tmp =  new DataTable();           //- Фильтр по товарам
        private DataTable dt_drugcode = new DataTable();            //- Коды товара
        private DataTable dt_pharmcode = new DataTable();           //- Коды аптек
        private DataTable dt_maks;                                  //- Аптеки Максавит
        public DataTable dt_price_perc= new DataTable();            //- Табличка с отклонениями

        
        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private SqlDataReader GetBrandTree(SqlConnection sqlcon)
        {
            SqlCommand com = sqlcon.CreateCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = @"pas_BrandTreeSel";

            return com.ExecuteReader();
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            cMain.Form1Control = this;
            conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
            competitors = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=Competitors;Integrated Security=True");
            this.Name = "Отчёт по остаткам по партиям";
            dateTimePicker1.Value = DateTime.Now.AddDays(-1);
            date_b = dateTimePicker1.Value.Date;
            dt_pharm = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_PharmSel", 300, null);
            //dt_drugs = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_AllDrugsSel", 300, null); ;
            listBox1.Items.Add("(Все аптеки)");
            listBox2.Items.Add("(Все товары)");
            dateTimePicker2.Value = DateTime.Now.AddYears(-2);
            dateTimePicker3.Value = DateTime.Now.AddYears(-2);
            date_sg_sup = dateTimePicker2.Value.Date;
            date_sg = dateTimePicker3.Value.Date;
            checkBox1.Checked = true;
            checkBox2.Checked = true;
            radioButton1.Checked = true;
            flag_rep = 0;
            groupBox6.Enabled = false;
        }

        private void StartCreateRep(object sender, EventArgs e)
        {
            if (dt_price_perc.Rows.Count == 0)
            {
                MessageBox.Show("Не введён процент отклонения цены");
                return;
            }
            dataGridView1.DataSource = null;
            dt_rep = null;
            dt_rep = new DataTable();
            del = new Deleg(CreateRep);
            this.Text += " Формируется";
            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
            panel1.Enabled = false;
            dataGridView1.Enabled = false;
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep.DefaultView));
        }

        private void CreateRep()
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                dt_drugcode = null;
                dt_drugcode = new DataTable();
                dt_drugcode.Columns.Add();
                dt_pharmcode = null;
                dt_pharmcode = new DataTable();
                dt_pharmcode.Columns.Add();

                if (dt_drug_tmp == null)
                {
                    foreach (DataRow dr in dt_drugs.Rows)
                    {
                        dt_drugcode.Rows.Add(dr["Код"]);
                    }
                }
                else
                {
                    foreach (DataRow dr in dt_drug_tmp.Rows)
                    {
                        dt_drugcode.Rows.Add(dr["Код"]);
                    }
                }

                foreach (DataRow dr in dt_pharm.Rows)
                {
                    dt_pharmcode.Rows.Add(dr["Код"]);
                }
                SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[8];
                par[0].name = "@date_b";
                par[0].value = date_b;
                par[1].name = "@date_sg_sup";
                if (checkBox1.Checked == true)
                {
                    par[1].value = date_sg_sup;
                }
                else
                {
                    par[1].value = null;
                }
                par[2].name = "@date_sg";
                if (checkBox2.Checked == true)
                {
                    par[2].value = date_sg;
                }
                else
                {
                    par[2].value = null;
                }
                par[3].name = "@flag_rep";
                par[3].value = flag_rep;
                par[4].name = "@pharm_max";
                par[4].value = pharm_maks;
                par[5].name = "@pharm";
                par[5].value = dt_pharmcode;
                par[6].name = "@drug";
                par[6].value = dt_drugcode;
                par[7].name = "@bal_drugs";
                par[7].value = dt_price_perc;

                dt_rep = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_DAC_BalanceParties", 1200, par);
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                conn.Close();
            }
        }

        private SqlDataReader RepProc(SqlConnection sqlcon)//метод для вызова хранимки
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = @"sp_name"; //имя хранимой процедуры
            sqlcom.CommandTimeout = 300;

            

            return sqlcom.ExecuteReader();
        }

        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "csv-file";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {   
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        sw.Write(col.HeaderText + "\t");
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        sw.Write(table[col_n[y], i].Value + "\t");
                    sw.Write(" \r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            //- Дата отчёта
            date_b = dateTimePicker1.Value.Date;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //- Ввод процента отклонения
            dt_price_perc = null;
            dt_price_perc = new DataTable();
            dt_price_perc.Columns.Add();
            dt_price_perc.Columns.Add();
            dt_price_perc.Columns.Add();
            Form2 form = new Form2();
            form.ShowDialog();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            //- Фильтр по аптекам
            dt_pharm = null;
            try
            {
                PharmFilter.PharmFilter f3 = new PharmFilter.PharmFilter();

                if (dt_pharm == null)
                {
                    SqlCommand myCommand = conn.CreateCommand();
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.CommandText = @"pas_PharmSel";
                    if (conn.State == ConnectionState.Closed) conn.Open();
                    dt_pharm = new DataTable();
                    dt_pharm.Load(myCommand.ExecuteReader());
                    foreach (DataColumn dc in dt_pharm.Columns)
                    {
                        if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                        else dc.ReadOnly = false;
                    }
                    conn.Close();
                }
                f3.namechcol = namechcol;
                f3.Text = "Фильтр по аптекам";
                f3.sortcoumn = "Аптека";
                f3.dataGridView1.DataSource = dt_pharm.DefaultView;
                f3.ShowDialog();
                listBox1.Items.Clear();
                foreach (DataRow dr in dt_pharm.Rows)
                {
                    if (Convert.ToBoolean(dr[namechcol]) == true)
                    {
                        listBox1.Items.Add(dr[f3.sortcoumn]);
                    }
                }
                if (listBox1.Items.Count == 0) listBox1.Items.Add("(Все аптеки)");
                f3.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            //- Фильтр по товару
            dt_drug_tmp = null;
            dt_drug_tmp = new DataTable();
            DrugsFilter.DrugsFilter f2 = new DrugsFilter.DrugsFilter();
            DrugsFilter.DrugsFilter.StorProcParam[] spp = new DrugsFilter.DrugsFilter.StorProcParam[1];

            spp[0] = f2.StorProcParamIns("@arch", SqlDbType.VarChar, 20, "jhjhjh");

            SqlCommand myCommand = new SqlCommand();
            myCommand = f2.StorProc(myCommand, conn, "pas_AllDrugsSel", spp);

            if (dt_drugs == null)
            {
                try
                {
                    if (conn.State == ConnectionState.Closed) conn.Open();
                    dt_drugs = new DataTable();
                    dt_drugs.Load(myCommand.ExecuteReader());
                    foreach (DataColumn dc in dt_drugs.Columns)
                    {
                        if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                        else dc.ReadOnly = false;
                    }
                    dt_brand = new DataTable();
                    dt_brand.Load(GetBrandTree(conn));
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                conn.Close();
                f2.rb = true;
            }
            if (dt_drug_tmp == null)
            {
                try
                {
                    if (conn.State == ConnectionState.Closed) conn.Open();
                    dt_drug_tmp = new DataTable();
                    dt_drug_tmp.Load(myCommand.ExecuteReader());
                    foreach (DataColumn dc in dt_drug_tmp.Columns)
                    {
                        if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                        else dc.ReadOnly = false;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                conn.Close();
            }
            f2.sqlconn = conn;
            f2.namechcol = namechcol;
            f2.dt_drug = dt_drugs;
            f2.dt_drug_tmp = dt_drug_tmp;
            if (dt_drug_tmp.Rows.Count == 0)
                f2.dataGridView1.DataSource = f2.dt_drug.DefaultView;
            else
                f2.dataGridView1.DataSource = f2.dt_drug_tmp.DefaultView;
            f2.dt_br = dt_brand;
            f2.ShowDialog();
            listBox2.Items.Clear();
            foreach (DataRow dr in dt_drug_tmp.Rows)
            {
                if (Convert.ToBoolean(dr[namechcol]) == true && dr.RowState != DataRowState.Deleted) listBox2.Items.Add(dr["Товар"]);
            }
            if (listBox2.Items.Count == 0) listBox2.Items.Add("(Все товары)");
            f2.Close();
        }

        private void dateTimePicker2_ValueChanged(object sender, EventArgs e)
        {
            //- Не учитывать цену со сроком годности поставщика
            date_sg_sup = dateTimePicker2.Value.Date;
        }

        private void dateTimePicker3_ValueChanged(object sender, EventArgs e)
        {
            //- Не учитывать цену со сроком годности переоцененного товара
            date_sg = dateTimePicker3.Value.Date;
        }

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            //- Отчёт - какой товар не в рынке относительно текущих закупочных цен
            if (radioButton1.Checked == true)
            {
                flag_rep = 0;
                groupBox6.Enabled = false;
            }
        }

        private void radioButton2_CheckedChanged(object sender, EventArgs e)
        {
            //- Отчёт - какой товар не в рынке среди наших аптек с тем же ЦО
            if (radioButton2.Checked == true)
            {
                flag_rep = 1;
                groupBox6.Enabled = true;
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            //- Выбрать аптеки максавит
            try
            {
                PharmFilter.PharmFilter f3 = new PharmFilter.PharmFilter();

                if (dt_maks == null)
                {
                    SqlCommand myCommand = competitors.CreateCommand();
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.CommandText = @"pas_CompPharmSel";
                    myCommand.Parameters.AddWithValue("@wsid", 4);
                    if (competitors.State == ConnectionState.Closed) competitors.Open();
                    dt_maks = new DataTable();
                    dt_maks.Load(myCommand.ExecuteReader());
                    foreach (DataColumn dc in dt_maks.Columns)
                    {
                        if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                        else dc.ReadOnly = false;
                    }
                    competitors.Close();
                }
                f3.namechcol = namechcol;
                f3.Text = "Фильтр по аптекам конкурентов";
                f3.sortcoumn = "Аптека";
                f3.dataGridView1.DataSource = dt_maks.DefaultView;
                f3.ShowDialog();
                listBox3.Items.Clear();
                foreach (DataRow dr in dt_maks.Rows)
                {
                    if (Convert.ToBoolean(dr[namechcol]) == true)
                    {
                        pharm_maks = Convert.ToInt32(dr[4].ToString());
                        listBox3.Items.Add(dr[f3.sortcoumn] + "(" + dr[2] + ", " + dr[3] + ")");
                    }

                }
                if (listBox3.Items.Count == 0)
                {
                    listBox3.Items.Add("Выберите аптеки конкурентов");
                }
                f3.Close();
                f3 = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            // Экспорт в Excel
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "остатки по партиям";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

    }
}
